package org.liukai.servlet_ems.dao;

import org.liukai.servlet_ems.entity.po.Emp;
import org.liukai.servlet_ems.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @project servlet_ems
 * @create EmpDao.java
 *
 * @author liukai
 * @date 2016/1/18 15:41
 * @detail
 */
public class EmpDao {
    //查询所有emp
    public static List<Emp> queryEmpList() {
        Connection conn = DBUtil.getConnection();
        List<Emp> list = null;
        try {
            list = new ArrayList<Emp>();
            //创建PreparedStatement
            PreparedStatement ps = conn.prepareStatement("select * from liukai_emp");
            //执行sql
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                list.add(rowToEmp(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
        }
        return list;
    }

    //新增emp
    public static void addEmp(Emp emp) {
        Connection conn = DBUtil.getConnection();
        try {
            //创建PreparedStatement
            PreparedStatement ps = conn.prepareStatement("insert into liukai_emp(name, sal, age, marry)values(?,?,?,?);");
            //设置参数  第一个参数为上面sql里"？"的下标 第二个为替换值   注意：这里的下标以1开头
            ps.setString(1, emp.getName());
            ps.setDouble(2, emp.getSal());
            ps.setInt(3, emp.getAge());
            ps.setBoolean(4, emp.getMarry());
            //执行sql
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
        }
    }

    //删除emp
    public static void deleteEmp(Integer empId) {
        Connection conn = DBUtil.getConnection();
        try {
            //创建PreparedStatement
            PreparedStatement ps = conn.prepareStatement("delete from liukai_emp where empId = ?;");
            //设置参数  第一个参数为上面sql里"？"的下标 第二个为替换值   注意：这里的下标以1开头
            ps.setInt(1, empId);
            //执行sql
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
        }
    }

    //根据empId查单个emp
    public static Emp queryEmpById(Integer empId) {
        Connection conn = DBUtil.getConnection();
        Emp emp = null;
        try {
            //创建PreparedStatement
            PreparedStatement ps = conn.prepareStatement("select * from liukai_emp where empId = ?;");
            //设置参数  第一个参数为上面sql里"？"的下标 第二个为替换值   注意：这里的下标以1开头
            ps.setInt(1, empId);
            //执行sql
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                emp = rowToEmp(rs);
            }
            return emp;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
        }
        return emp;
    }

    //修改emp
    public static void updateEmp(Emp emp) {
        Connection conn = DBUtil.getConnection();
        try {
            //创建PreparedStatement
            PreparedStatement ps = conn.prepareStatement("update liukai_emp set name=?, sal=?, age=?, marry=? where empId=?;");
            //设置参数  第一个参数为上面sql里"？"的下标 第二个为替换值   注意：这里的下标以1开头
            ps.setString(1, emp.getName());
            ps.setDouble(2, emp.getSal());
            ps.setInt(3, emp.getAge());
            ps.setBoolean(4, emp.getMarry());
            ps.setInt(5,emp.getEmpId());
            //执行sql
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(conn);
        }
    }


    //将数据库每一行映射为Emp对象
    private static Emp rowToEmp(ResultSet rs) throws SQLException {
        Emp emp = new Emp();
        emp.setEmpId(rs.getInt("empId"));
        emp.setAge(rs.getInt("age"));
        emp.setName(rs.getString("name"));
        emp.setSal(rs.getDouble("sal"));
        emp.setMarry(rs.getBoolean("marry"));
        return emp;
    }


}
